<!DOCTYPE html>
<html lang=zh>
<head>
  <meta charset="utf-8">
  
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, minimum-scale=1, user-scalable=no, minimal-ui">
  <meta name="renderer" content="webkit">
  <meta http-equiv="Cache-Control" content="no-transform" />
  <meta http-equiv="Cache-Control" content="no-siteapp" />
  <meta name="apple-mobile-web-app-capable" content="yes">
  <meta name="apple-mobile-web-app-status-bar-style" content="black">
  <meta name="format-detection" content="telephone=no,email=no,adress=no">
  <!-- Color theme for statusbar -->
  <meta name="theme-color" content="#000000" />
  <!-- 强制页面在当前窗口以独立页面显示,防止别人在框架里调用页面 -->
  <meta http-equiv="window-target" content="_top" />
  
  <meta name="google-site-verification" content="XKEVBvaS3n3qpVoP-hQqQ2XJ9t43pt4B7J1sLZgRJG4" />
  
  
  <title>源码分析 | ClickHouse和他的朋友们（9）MySQL实时复制与实现 | DBKernel - 专注于分享数据库技术</title>
  <meta name="description" content="本文首发于 2020-07-28 21:50:10  《ClickHouse和他的朋友们》系列文章转载自圈内好友 BohuTANG 的博客，原文链接：https:&#x2F;&#x2F;bohutang.me&#x2F;2020&#x2F;07&#x2F;25&#x2F;clickhouse-and-friends-parser&#x2F;以下为正文。   很多人看到标题还以为自己走错了夜场，其实没有。 ClickHouse 可以挂载为 MySQL 的一个从库">
<meta property="og:type" content="article">
<meta property="og:title" content="源码分析 | ClickHouse和他的朋友们（9）MySQL实时复制与实现">
<meta property="og:url" content="http://dbkernel.github.io/2020/07/28/clickhouse-and-friends-09-mysql-replication/index.html">
<meta property="og:site_name" content="DBKernel">
<meta property="og:description" content="本文首发于 2020-07-28 21:50:10  《ClickHouse和他的朋友们》系列文章转载自圈内好友 BohuTANG 的博客，原文链接：https:&#x2F;&#x2F;bohutang.me&#x2F;2020&#x2F;07&#x2F;25&#x2F;clickhouse-and-friends-parser&#x2F;以下为正文。   很多人看到标题还以为自己走错了夜场，其实没有。 ClickHouse 可以挂载为 MySQL 的一个从库">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="http://dbkernel.github.io/2020/07/28/clickhouse-and-friends-09-mysql-replication/clickhouse-map-2020-materialzemysql.png">
<meta property="og:image" content="https://dbkernel-1306518848.cos.ap-beijing.myqcloud.com/wechat/my-wechat-official-account.png">
<meta property="article:published_time" content="2020-07-28T13:50:10.000Z">
<meta property="article:modified_time" content="2021-10-10T12:31:06.598Z">
<meta property="article:author" content="DBKernel">
<meta property="article:tag" content="MySQL">
<meta property="article:tag" content="ClickHouse和他的朋友们">
<meta property="article:tag" content="ClickHouse">
<meta property="article:tag" content="源码分析">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="http://dbkernel.github.io/2020/07/28/clickhouse-and-friends-09-mysql-replication/clickhouse-map-2020-materialzemysql.png">
  <!-- Canonical links -->
  <link rel="canonical" href="http://dbkernel.github.io/2020/07/28/clickhouse-and-friends-09-mysql-replication/index.html">
  
    <link rel="alternate" href="/atom.xml" title="DBKernel" type="application/atom+xml">
  
  
    <link rel="icon" href="/favicon.png" type="image/x-icon">
  
  
<link rel="stylesheet" href="../../../../css/style.css">

  
  
  
  
    <link rel="stylesheet" href="//cdn.jsdelivr.net/npm/gitalk@1.4.0/dist/gitalk.min.css">
  
<meta name="generator" content="Hexo 5.4.0"></head>


<body class="main-center" itemscope itemtype="http://schema.org/WebPage">
  <header class="header" itemscope itemtype="http://schema.org/WPHeader">
  <div class="slimContent">
    <div class="navbar-header">
      
      
      <div class="profile-block text-center">
        <a id="avatar" href="https://github.com/dbkernel" target="_blank">
          <img class="img-circle img-rotate" src="../../../../images/avatar.png" width="200" height="200">
        </a>
        <h2 id="name" class="hidden-xs hidden-sm">DBKernel</h2>
        <h3 id="title" class="hidden-xs hidden-sm hidden-md">资深数据库开发工程师</h3>
        <small id="location" class="text-muted hidden-xs hidden-sm"><i class="icon icon-map-marker"></i> Beijing, China</small>
      </div>
      
      <div class="search" id="search-form-wrap">

    <form class="search-form sidebar-form">
        <div class="input-group">
            <input type="text" class="search-form-input form-control" placeholder="搜索" />
            <span class="input-group-btn">
                <button type="submit" class="search-form-submit btn btn-flat" onclick="return false;"><i class="icon icon-search"></i></button>
            </span>
        </div>
    </form>
    <div class="ins-search">
  <div class="ins-search-mask"></div>
  <div class="ins-search-container">
    <div class="ins-input-wrapper">
      <input type="text" class="ins-search-input" placeholder="想要查找什么..." x-webkit-speech />
      <button type="button" class="close ins-close ins-selectable" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
    </div>
    <div class="ins-section-wrapper">
      <div class="ins-section-container"></div>
    </div>
  </div>
</div>


</div>
      <button class="navbar-toggle collapsed" type="button" data-toggle="collapse" data-target="#main-navbar" aria-controls="main-navbar" aria-expanded="false">
        <span class="sr-only">Toggle navigation</span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </button>
    </div>
    <nav id="main-navbar" class="collapse navbar-collapse" itemscope itemtype="http://schema.org/SiteNavigationElement" role="navigation">
      <ul class="nav navbar-nav main-nav ">
        
        
        <li class="menu-item menu-item-home">
          <a href="../../../../.">
            
            <i class="icon icon-home-fill"></i>
            
            <span class="menu-title">首页</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-archives">
          <a href="../../../../archives">
            
            <i class="icon icon-archives-fill"></i>
            
            <span class="menu-title">归档</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-categories">
          <a href="../../../../categories">
            
            <i class="icon icon-folder"></i>
            
            <span class="menu-title">分类</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-tags">
          <a href="../../../../tags">
            
            <i class="icon icon-tags"></i>
            
            <span class="menu-title">标签</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-repository">
          <a href="../../../../repository">
            
            <i class="icon icon-project"></i>
            
            <span class="menu-title">项目</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-links">
          <a href="../../../../links">
            
            <i class="icon icon-friendship"></i>
            
            <span class="menu-title">友链</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-about">
          <a href="../../../../about">
            
            <i class="icon icon-cup-fill"></i>
            
            <span class="menu-title">关于</span>
          </a>
        </li>
        
      </ul>
      
	
    <ul class="social-links">
    	
        <li><a href="../../../../https:/github.com/dbkernel" target="_blank" title="Github" data-toggle=tooltip data-placement=top><i class="icon icon-github"></i></a></li>
        
        <li><a href="../../../../atom.xml" target="_blank" title="Rss" data-toggle=tooltip data-placement=top><i class="icon icon-rss"></i></a></li>
        
    </ul>

    </nav>
  </div>
</header>

  
    <aside class="sidebar" itemscope itemtype="http://schema.org/WPSideBar">
  <div class="slimContent">
    
      <div class="widget">
    <h3 class="widget-title">公告</h3>
    <div class="widget-body">
        <div id="board">
            <div class="content">
                <p>本站专注于分享关系型数据库及分布式数据库相关技术！如需深入交流，可添加我的微信或关注我的公众号数据库内核：</br><img src="/images/wechat-me.jpeg" width="130" height="130"/> <img src="/images/wechat-gzh.jpeg" width="130" height="130"/></p>
            </div>
        </div>
    </div>
</div>

    
      
  <div class="widget">
    <h3 class="widget-title">分类</h3>
    <div class="widget-body">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="../../../../categories/ClickHouse/">ClickHouse</a><span class="category-list-count">14</span></li><li class="category-list-item"><a class="category-list-link" href="../../../../categories/C%E8%AF%AD%E8%A8%80/">C语言</a><span class="category-list-count">8</span></li><li class="category-list-item"><a class="category-list-link" href="../../../../categories/GreenPlum/">GreenPlum</a><span class="category-list-count">3</span></li><li class="category-list-item"><a class="category-list-link" href="../../../../categories/Linux/">Linux</a><span class="category-list-count">5</span></li><li class="category-list-item"><a class="category-list-link" href="../../../../categories/MySQL/">MySQL</a><span class="category-list-count">4</span></li><li class="category-list-item"><a class="category-list-link" href="../../../../categories/PostgreSQL/">PostgreSQL</a><span class="category-list-count">7</span></li><li class="category-list-item"><a class="category-list-link" href="../../../../categories/Postgres-X2/">Postgres-X2</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="../../../../categories/%E7%BC%96%E8%AF%91%E8%B0%83%E8%AF%95/">编译调试</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="../../../../categories/%E9%80%9A%E7%94%A8/">通用</a><span class="category-list-count">1</span></li></ul>
    </div>
  </div>


    
      
  <div class="widget">
    <h3 class="widget-title">标签</h3>
    <div class="widget-body">
      <ul class="tag-list" itemprop="keywords"><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/APUE/" rel="tag">APUE</a><span class="tag-list-count">4</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/B-Tree/" rel="tag">B-Tree</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/ClickHouse/" rel="tag">ClickHouse</a><span class="tag-list-count">14</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/ClickHouse%E5%92%8C%E4%BB%96%E7%9A%84%E6%9C%8B%E5%8F%8B%E4%BB%AC/" rel="tag">ClickHouse和他的朋友们</a><span class="tag-list-count">14</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/Count/" rel="tag">Count</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/C%E8%AF%AD%E8%A8%80/" rel="tag">C语言</a><span class="tag-list-count">10</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/DAG-Scheduler/" rel="tag">DAG Scheduler</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/GreenPlum/" rel="tag">GreenPlum</a><span class="tag-list-count">3</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/HEAP%E5%BC%95%E6%93%8E/" rel="tag">HEAP引擎</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/LICENCE/" rel="tag">LICENCE</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/LSM-Tree/" rel="tag">LSM-Tree</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/Linux/" rel="tag">Linux</a><span class="tag-list-count">7</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/MEMORY%E5%BC%95%E6%93%8E/" rel="tag">MEMORY引擎</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/Makefile/" rel="tag">Makefile</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/Materialized-View/" rel="tag">Materialized View</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/MergeTree/" rel="tag">MergeTree</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/MySQL/" rel="tag">MySQL</a><span class="tag-list-count">8</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/Parser/" rel="tag">Parser</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/Percona/" rel="tag">Percona</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/PostgreSQL/" rel="tag">PostgreSQL</a><span class="tag-list-count">11</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/Postgres-X2/" rel="tag">Postgres-X2</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/Postgres-XC/" rel="tag">Postgres-XC</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/RadonDB/" rel="tag">RadonDB</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/ReplicatedMergeTree/" rel="tag">ReplicatedMergeTree</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/Select/" rel="tag">Select</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/UNIX/" rel="tag">UNIX</a><span class="tag-list-count">3</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/WAL/" rel="tag">WAL</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/Xenon/" rel="tag">Xenon</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/Xtrabackup/" rel="tag">Xtrabackup</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/auto-increment/" rel="tag">auto_increment</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/crontab/" rel="tag">crontab</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/daemon/" rel="tag">daemon</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/df/" rel="tag">df</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/du/" rel="tag">du</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/gcc/" rel="tag">gcc</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/gcov/" rel="tag">gcov</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/getopt/" rel="tag">getopt</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/github/" rel="tag">github</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/group-by/" rel="tag">group by</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/lcov/" rel="tag">lcov</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/libpq/" rel="tag">libpq</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/pg-constraint/" rel="tag">pg_constraint</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/pg-depend/" rel="tag">pg_depend</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/pgbench/" rel="tag">pgbench</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/pipeline/" rel="tag">pipeline</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/processor/" rel="tag">processor</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/shell/" rel="tag">shell</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E4%B8%BB%E4%BB%8E%E5%90%8C%E6%AD%A5/" rel="tag">主从同步</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E4%BC%98%E5%8C%96%E5%99%A8/" rel="tag">优化器</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E5%86%85%E5%AD%98%E5%88%86%E9%85%8D/" rel="tag">内存分配</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E5%86%85%E5%AD%98%E7%AE%A1%E7%90%86/" rel="tag">内存管理</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E5%9B%9E%E5%BD%92%E6%B5%8B%E8%AF%95/" rel="tag">回归测试</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E5%AD%97%E8%8A%82%E5%AF%B9%E9%BD%90/" rel="tag">字节对齐</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E5%AD%97%E8%8A%82%E5%BA%8F/" rel="tag">字节序</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E5%AD%98%E5%82%A8%E8%AE%A1%E7%AE%97%E5%88%86%E7%A6%BB/" rel="tag">存储计算分离</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E5%B9%B6%E8%A1%8C%E6%9F%A5%E8%AF%A2/" rel="tag">并行查询</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E5%BC%80%E6%BA%90%E5%8D%8F%E8%AE%AE/" rel="tag">开源协议</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E5%BC%80%E6%BA%90%E8%AE%B8%E5%8F%AF%E8%AF%81/" rel="tag">开源许可证</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E6%9C%AC%E5%9C%B0%E4%BA%8B%E5%8A%A1/" rel="tag">本地事务</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E6%B5%8B%E8%AF%95/" rel="tag">测试</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E6%BA%90%E7%A0%81%E5%88%86%E6%9E%90/" rel="tag">源码分析</a><span class="tag-list-count">15</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E7%B3%BB%E7%BB%9F%E8%BF%90%E7%BB%B4/" rel="tag">系统运维</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E7%BC%96%E8%AF%91%E5%99%A8/" rel="tag">编译器</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E7%BD%91%E7%BB%9C%E5%BA%8F/" rel="tag">网络序</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E7%BD%91%E7%BB%9C%E7%BC%96%E7%A8%8B/" rel="tag">网络编程</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="../../../../tags/%E9%97%AE%E9%A2%98%E5%AE%9A%E4%BD%8D/" rel="tag">问题定位</a><span class="tag-list-count">2</span></li></ul>
    </div>
  </div>


    
      
  <div class="widget">
    <h3 class="widget-title">标签云</h3>
    <div class="widget-body tagcloud">
      <a href="../../../../tags/APUE/" style="font-size: 13.33px;">APUE</a> <a href="../../../../tags/B-Tree/" style="font-size: 13px;">B-Tree</a> <a href="../../../../tags/ClickHouse/" style="font-size: 13.89px;">ClickHouse</a> <a href="../../../../tags/ClickHouse%E5%92%8C%E4%BB%96%E7%9A%84%E6%9C%8B%E5%8F%8B%E4%BB%AC/" style="font-size: 13.89px;">ClickHouse和他的朋友们</a> <a href="../../../../tags/Count/" style="font-size: 13px;">Count</a> <a href="../../../../tags/C%E8%AF%AD%E8%A8%80/" style="font-size: 13.67px;">C语言</a> <a href="../../../../tags/DAG-Scheduler/" style="font-size: 13px;">DAG Scheduler</a> <a href="../../../../tags/GreenPlum/" style="font-size: 13.22px;">GreenPlum</a> <a href="../../../../tags/HEAP%E5%BC%95%E6%93%8E/" style="font-size: 13px;">HEAP引擎</a> <a href="../../../../tags/LICENCE/" style="font-size: 13px;">LICENCE</a> <a href="../../../../tags/LSM-Tree/" style="font-size: 13px;">LSM-Tree</a> <a href="../../../../tags/Linux/" style="font-size: 13.44px;">Linux</a> <a href="../../../../tags/MEMORY%E5%BC%95%E6%93%8E/" style="font-size: 13px;">MEMORY引擎</a> <a href="../../../../tags/Makefile/" style="font-size: 13px;">Makefile</a> <a href="../../../../tags/Materialized-View/" style="font-size: 13px;">Materialized View</a> <a href="../../../../tags/MergeTree/" style="font-size: 13.11px;">MergeTree</a> <a href="../../../../tags/MySQL/" style="font-size: 13.56px;">MySQL</a> <a href="../../../../tags/Parser/" style="font-size: 13px;">Parser</a> <a href="../../../../tags/Percona/" style="font-size: 13px;">Percona</a> <a href="../../../../tags/PostgreSQL/" style="font-size: 13.78px;">PostgreSQL</a> <a href="../../../../tags/Postgres-X2/" style="font-size: 13px;">Postgres-X2</a> <a href="../../../../tags/Postgres-XC/" style="font-size: 13px;">Postgres-XC</a> <a href="../../../../tags/RadonDB/" style="font-size: 13px;">RadonDB</a> <a href="../../../../tags/ReplicatedMergeTree/" style="font-size: 13px;">ReplicatedMergeTree</a> <a href="../../../../tags/Select/" style="font-size: 13px;">Select</a> <a href="../../../../tags/UNIX/" style="font-size: 13.22px;">UNIX</a> <a href="../../../../tags/WAL/" style="font-size: 13.11px;">WAL</a> <a href="../../../../tags/Xenon/" style="font-size: 13px;">Xenon</a> <a href="../../../../tags/Xtrabackup/" style="font-size: 13px;">Xtrabackup</a> <a href="../../../../tags/auto-increment/" style="font-size: 13px;">auto_increment</a> <a href="../../../../tags/crontab/" style="font-size: 13px;">crontab</a> <a href="../../../../tags/daemon/" style="font-size: 13px;">daemon</a> <a href="../../../../tags/df/" style="font-size: 13px;">df</a> <a href="../../../../tags/du/" style="font-size: 13px;">du</a> <a href="../../../../tags/gcc/" style="font-size: 13px;">gcc</a> <a href="../../../../tags/gcov/" style="font-size: 13px;">gcov</a> <a href="../../../../tags/getopt/" style="font-size: 13px;">getopt</a> <a href="../../../../tags/github/" style="font-size: 13px;">github</a> <a href="../../../../tags/group-by/" style="font-size: 13px;">group by</a> <a href="../../../../tags/lcov/" style="font-size: 13px;">lcov</a> <a href="../../../../tags/libpq/" style="font-size: 13px;">libpq</a> <a href="../../../../tags/pg-constraint/" style="font-size: 13px;">pg_constraint</a> <a href="../../../../tags/pg-depend/" style="font-size: 13px;">pg_depend</a> <a href="../../../../tags/pgbench/" style="font-size: 13px;">pgbench</a> <a href="../../../../tags/pipeline/" style="font-size: 13px;">pipeline</a> <a href="../../../../tags/processor/" style="font-size: 13px;">processor</a> <a href="../../../../tags/shell/" style="font-size: 13px;">shell</a> <a href="../../../../tags/%E4%B8%BB%E4%BB%8E%E5%90%8C%E6%AD%A5/" style="font-size: 13.11px;">主从同步</a> <a href="../../../../tags/%E4%BC%98%E5%8C%96%E5%99%A8/" style="font-size: 13px;">优化器</a> <a href="../../../../tags/%E5%86%85%E5%AD%98%E5%88%86%E9%85%8D/" style="font-size: 13px;">内存分配</a> <a href="../../../../tags/%E5%86%85%E5%AD%98%E7%AE%A1%E7%90%86/" style="font-size: 13px;">内存管理</a> <a href="../../../../tags/%E5%9B%9E%E5%BD%92%E6%B5%8B%E8%AF%95/" style="font-size: 13px;">回归测试</a> <a href="../../../../tags/%E5%AD%97%E8%8A%82%E5%AF%B9%E9%BD%90/" style="font-size: 13.11px;">字节对齐</a> <a href="../../../../tags/%E5%AD%97%E8%8A%82%E5%BA%8F/" style="font-size: 13.11px;">字节序</a> <a href="../../../../tags/%E5%AD%98%E5%82%A8%E8%AE%A1%E7%AE%97%E5%88%86%E7%A6%BB/" style="font-size: 13px;">存储计算分离</a> <a href="../../../../tags/%E5%B9%B6%E8%A1%8C%E6%9F%A5%E8%AF%A2/" style="font-size: 13px;">并行查询</a> <a href="../../../../tags/%E5%BC%80%E6%BA%90%E5%8D%8F%E8%AE%AE/" style="font-size: 13px;">开源协议</a> <a href="../../../../tags/%E5%BC%80%E6%BA%90%E8%AE%B8%E5%8F%AF%E8%AF%81/" style="font-size: 13px;">开源许可证</a> <a href="../../../../tags/%E6%9C%AC%E5%9C%B0%E4%BA%8B%E5%8A%A1/" style="font-size: 13px;">本地事务</a> <a href="../../../../tags/%E6%B5%8B%E8%AF%95/" style="font-size: 13px;">测试</a> <a href="../../../../tags/%E6%BA%90%E7%A0%81%E5%88%86%E6%9E%90/" style="font-size: 14px;">源码分析</a> <a href="../../../../tags/%E7%B3%BB%E7%BB%9F%E8%BF%90%E7%BB%B4/" style="font-size: 13px;">系统运维</a> <a href="../../../../tags/%E7%BC%96%E8%AF%91%E5%99%A8/" style="font-size: 13px;">编译器</a> <a href="../../../../tags/%E7%BD%91%E7%BB%9C%E5%BA%8F/" style="font-size: 13.11px;">网络序</a> <a href="../../../../tags/%E7%BD%91%E7%BB%9C%E7%BC%96%E7%A8%8B/" style="font-size: 13px;">网络编程</a> <a href="../../../../tags/%E9%97%AE%E9%A2%98%E5%AE%9A%E4%BD%8D/" style="font-size: 13.11px;">问题定位</a>
    </div>
  </div>

    
      
  <div class="widget">
    <h3 class="widget-title">归档</h3>
    <div class="widget-body">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2021/08/">八月 2021</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2021/01/">一月 2021</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2020/09/">九月 2020</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2020/08/">八月 2020</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2020/07/">七月 2020</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2020/06/">六月 2020</a><span class="archive-list-count">6</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2020/05/">五月 2020</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2019/12/">十二月 2019</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2019/04/">四月 2019</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2018/09/">九月 2018</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2016/11/">十一月 2016</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2016/04/">四月 2016</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2016/03/">三月 2016</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2016/01/">一月 2016</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2015/12/">十二月 2015</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2015/11/">十一月 2015</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2015/05/">五月 2015</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2015/01/">一月 2015</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2014/08/">八月 2014</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2014/07/">七月 2014</a><span class="archive-list-count">5</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2014/03/">三月 2014</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="../../../../archives/2014/01/">一月 2014</a><span class="archive-list-count">1</span></li></ul>
    </div>
  </div>


    
      
  <div class="widget">
    <h3 class="widget-title">最新文章</h3>
    <div class="widget-body">
      <ul class="recent-post-list list-unstyled no-thumbnail">
        
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="../../../../categories/%E9%80%9A%E7%94%A8/">通用</a>
              </p>
              <p class="item-title">
                <a href="../../../../2021/08/19/how-to-choose-open-source-licence/" class="title">技术分享 | 如何为你的代码选择一个合适的开源协议？</a>
              </p>
              <p class="item-date">
                <time datetime="2021-08-18T16:37:15.000Z" itemprop="datePublished">2021-08-19</time>
              </p>
            </div>
          </li>
          
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="../../../../categories/ClickHouse/">ClickHouse</a>
              </p>
              <p class="item-title">
                <a href="../../../../2021/01/26/clickhouse-and-friends-15-groupby/" class="title">源码分析 | ClickHouse和他的朋友们（15）Group By 为什么这么快</a>
              </p>
              <p class="item-date">
                <time datetime="2021-01-26T13:31:12.000Z" itemprop="datePublished">2021-01-26</time>
              </p>
            </div>
          </li>
          
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="../../../../categories/ClickHouse/">ClickHouse</a>
              </p>
              <p class="item-title">
                <a href="../../../09/21/clickhouse-and-friends-14-compute-storage/" class="title">源码分析 | ClickHouse和他的朋友们（14）存储计算分离方案与实现</a>
              </p>
              <p class="item-date">
                <time datetime="2020-09-21T14:01:12.000Z" itemprop="datePublished">2020-09-21</time>
              </p>
            </div>
          </li>
          
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="../../../../categories/ClickHouse/">ClickHouse</a>
              </p>
              <p class="item-title">
                <a href="../../../09/15/clickhouse-and-friends-13-replicated-merge-tree/" class="title">源码分析 | ClickHouse和他的朋友们（13）ReplicatedMergeTree表引擎及同步机制</a>
              </p>
              <p class="item-date">
                <time datetime="2020-09-15T12:15:14.000Z" itemprop="datePublished">2020-09-15</time>
              </p>
            </div>
          </li>
          
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="../../../../categories/ClickHouse/">ClickHouse</a>
              </p>
              <p class="item-title">
                <a href="../../../09/03/clickhouse-and-friends-12-materialized-view/" class="title">源码分析 | ClickHouse和他的朋友们（12）神奇的物化视图(Materialized View)与原理</a>
              </p>
              <p class="item-date">
                <time datetime="2020-09-03T13:22:14.000Z" itemprop="datePublished">2020-09-03</time>
              </p>
            </div>
          </li>
          
      </ul>
    </div>
  </div>
  

    
  </div>
</aside>

  
  
<aside class="sidebar sidebar-toc collapse" id="collapseToc" itemscope itemtype="http://schema.org/WPSideBar">
  <div class="slimContent">
    <nav id="toc" class="article-toc">
      <h3 class="toc-title">文章目录</h3>
      <ol class="toc"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BB%A3%E7%A0%81%E8%8E%B7%E5%8F%96"><span class="toc-number">1.</span> <span class="toc-text">代码获取</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#MySQL-Master"><span class="toc-number">2.</span> <span class="toc-text">MySQL Master</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#ClickHouse-Slave"><span class="toc-number">3.</span> <span class="toc-text">ClickHouse Slave</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#Delete"><span class="toc-number">4.</span> <span class="toc-text">Delete</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#Update"><span class="toc-number">5.</span> <span class="toc-text">Update</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%80%A7%E8%83%BD%E6%B5%8B%E8%AF%95"><span class="toc-number">6.</span> <span class="toc-text">性能测试</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E6%B5%8B%E8%AF%95%E7%8E%AF%E5%A2%83"><span class="toc-number">6.1.</span> <span class="toc-text">测试环境</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%85%A8%E9%87%8F%E6%80%A7%E8%83%BD"><span class="toc-number">6.2.</span> <span class="toc-text">全量性能</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%A2%9E%E9%87%8F%E6%80%A7%E8%83%BD-%E5%AE%9E%E6%97%B6%E5%90%8C%E6%AD%A5"><span class="toc-number">6.3.</span> <span class="toc-text">增量性能(实时同步)</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AE%9E%E7%8E%B0%E6%9C%BA%E5%88%B6"><span class="toc-number">7.</span> <span class="toc-text">实现机制</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#DDL"><span class="toc-number">7.1.</span> <span class="toc-text">DDL</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#Update%E5%92%8CDelete"><span class="toc-number">7.2.</span> <span class="toc-text">Update和Delete</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#Query"><span class="toc-number">7.3.</span> <span class="toc-text">Query</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%B9%B6%E8%A1%8C%E5%9B%9E%E6%94%BE"><span class="toc-number">8.</span> <span class="toc-text">并行回放</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%AF%BB%E5%8F%96%E6%9C%80%E6%96%B0"><span class="toc-number">9.</span> <span class="toc-text">读取最新</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E4%B8%80%E8%87%B4%E6%80%A7"><span class="toc-number">10.</span> <span class="toc-text">数据一致性</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%80%BB%E7%BB%93"><span class="toc-number">11.</span> <span class="toc-text">总结</span></a></li></ol>
    </nav>
  </div>
</aside>

<main class="main" role="main">
  <div class="content">
  <article id="post-clickhouse-and-friends-09-mysql-replication" class="article article-type-post" itemscope itemtype="http://schema.org/BlogPosting">
    
    <div class="article-header">
      
        
  
    <h1 class="article-title" itemprop="name">
      源码分析 | ClickHouse和他的朋友们（9）MySQL实时复制与实现
    </h1>
  

      
      <div class="article-meta">
        <span class="article-date">
    <i class="icon icon-calendar-check"></i>
	<a href="" class="article-date">
	  <time datetime="2020-07-28T13:50:10.000Z" itemprop="datePublished">2020-07-28</time>
	</a>
</span>
        
  <span class="article-category">
    <i class="icon icon-folder"></i>
    <a class="article-category-link" href="../../../../categories/ClickHouse/">ClickHouse</a>
  </span>

        
  <span class="article-tag">
    <i class="icon icon-tags"></i>
	<a class="article-tag-link-link" href="../../../../tags/ClickHouse/" rel="tag">ClickHouse</a>, <a class="article-tag-link-link" href="../../../../tags/ClickHouse%E5%92%8C%E4%BB%96%E7%9A%84%E6%9C%8B%E5%8F%8B%E4%BB%AC/" rel="tag">ClickHouse和他的朋友们</a>, <a class="article-tag-link-link" href="../../../../tags/MySQL/" rel="tag">MySQL</a>, <a class="article-tag-link-link" href="../../../../tags/%E6%BA%90%E7%A0%81%E5%88%86%E6%9E%90/" rel="tag">源码分析</a>
  </span>


        
	<span class="article-read hidden-xs">
	    <i class="icon icon-eye-fill" aria-hidden="true"></i>
	    <span id="busuanzi_container_page_pv">
			<span id="busuanzi_value_page_pv">0</span>
		</span>
	</span>


        <span class="post-comment"><i class="icon icon-comment"></i> <a href="#comments" class="article-comment-link">评论</a></span>
        
	
		<span class="post-wordcount hidden-xs" itemprop="wordCount">字数统计: 3k(字)</span>
	
	
		<span class="post-readcount hidden-xs" itemprop="timeRequired">阅读时长: 13(分)</span>
	

      </div>
    </div>
    <div class="article-entry marked-body" itemprop="articleBody">
      
        <span id="more"></span>

<p><strong>本文首发于 2020-07-28 21:50:10</strong></p>
<blockquote>
<p>《ClickHouse和他的朋友们》系列文章转载自圈内好友 <a target="_blank" rel="noopener" href="https://bohutang.me/">BohuTANG</a> 的博客，原文链接：<br><a target="_blank" rel="noopener" href="https://bohutang.me/2020/07/25/clickhouse-and-friends-parser/">https://bohutang.me/2020/07/25/clickhouse-and-friends-parser/</a><br>以下为正文。</p>
</blockquote>
<p><img src="clickhouse-map-2020-materialzemysql.png" alt="clickhouse-map-2020-materialzemysql.png"></p>
<p>很多人看到标题还以为自己走错了夜场，其实没有。</p>
<p>ClickHouse 可以挂载为 MySQL 的一个从库 ，先全量再增量的实时同步 MySQL 数据，这个功能可以说是今年最亮眼、最刚需的功能，基于它我们可以轻松的打造一套企业级解决方案，让 OLTP 和 OLAP 的融合从此不再头疼。</p>
<p>目前支持 MySQL 5.6/5.7/8.0 版本，兼容 Delete/Update 语句，及大部分常用的 DDL 操作。</p>
<p><a target="_blank" rel="noopener" href="https://github.com/ClickHouse/ClickHouse/pull/10851">代码</a>已经合并到 upstream master 分支，预计在20.8版本作为experimental 功能发布。</p>
<p>毕竟是两个异构生态的融合，仍然有不少的工作要做，同时也期待着社区用户的反馈，以加速迭代。</p>
<h3 id="代码获取"><a href="#代码获取" class="headerlink" title="代码获取"></a>代码获取</h3><p>获取 <a target="_blank" rel="noopener" href="https://github.com/ClickHouse/ClickHouse">clickhouse/master</a> 代码编译即可，方法见 <a target="_blank" rel="noopener" href="https://bohutang.me/2020/06/05/clickhouse-and-friends-development/">ClickHouse和他的朋友们（1）编译、开发、测试</a>…</p>
<h3 id="MySQL-Master"><a href="#MySQL-Master" class="headerlink" title="MySQL Master"></a>MySQL Master</h3><p>我们需要一个开启 binlog 的 MySQL 作为 master:</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">docker run -d -e MYSQL_ROOT_PASSWORD=123 mysql:5.7 mysqld --datadir=/var/lib/mysql --server-id=1 --log-bin=/var/lib/mysql/mysql-bin.log --gtid-mode=ON --enforce-gtid-consistency</span><br></pre></td></tr></table></figure>

<p>创建数据库和表，并写入数据:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">create</span> database ckdb;</span><br><span class="line">mysql<span class="operator">&gt;</span> use ckdb;</span><br><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">create</span> <span class="keyword">table</span> t1(a <span class="type">int</span> <span class="keyword">not</span> <span class="keyword">null</span> <span class="keyword">primary</span> key, b <span class="type">int</span>);</span><br><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">insert</span> <span class="keyword">into</span> t1 <span class="keyword">values</span>(<span class="number">1</span>,<span class="number">1</span>),(<span class="number">2</span>,<span class="number">2</span>);</span><br><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t1;</span><br><span class="line"><span class="operator">+</span><span class="comment">---+------+</span></span><br><span class="line"><span class="operator">|</span> a <span class="operator">|</span> b    <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">---+------+</span></span><br><span class="line"><span class="operator">|</span> <span class="number">1</span> <span class="operator">|</span>    <span class="number">1</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> <span class="number">2</span> <span class="operator">|</span>    <span class="number">2</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">---+------+</span></span><br><span class="line"><span class="number">2</span> <span class="keyword">rows</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br></pre></td></tr></table></figure>

<h3 id="ClickHouse-Slave"><a href="#ClickHouse-Slave" class="headerlink" title="ClickHouse Slave"></a>ClickHouse Slave</h3><p>目前以 database 为单位进行复制，不同的 database 可以来自不同的 MySQL master，这样就可以实现多个 MySQL 源数据同步到一个 ClickHouse 做 OLAP 分析功能。</p>
<p>首先开启体验开关:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">clickhouse :) <span class="keyword">SET</span> allow_experimental_database_materialize_mysql<span class="operator">=</span><span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<p>创建一个复制通道：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">clickhouse :) <span class="keyword">CREATE</span> DATABASE ckdb ENGINE <span class="operator">=</span> MaterializeMySQL(<span class="string">&#x27;172.17.0.2:3306&#x27;</span>, <span class="string">&#x27;ckdb&#x27;</span>, <span class="string">&#x27;root&#x27;</span>, <span class="string">&#x27;123&#x27;</span>);</span><br><span class="line">clickhouse :) use ckdb;</span><br><span class="line">clickhouse :) <span class="keyword">show</span> tables;</span><br><span class="line">┌─name─┐</span><br><span class="line">│ t1   │</span><br><span class="line">└──────┘</span><br><span class="line">clickhouse :) <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t1;</span><br><span class="line">┌─a─┬─b─┐</span><br><span class="line">│ <span class="number">1</span> │ <span class="number">1</span> │</span><br><span class="line">└───┴───┘</span><br><span class="line">┌─a─┬─b─┐</span><br><span class="line">│ <span class="number">2</span> │ <span class="number">2</span> │</span><br><span class="line">└───┴───┘</span><br><span class="line"></span><br><span class="line"><span class="number">2</span> <span class="keyword">rows</span> <span class="keyword">in</span> set. Elapsed: <span class="number">0.017</span> sec.</span><br></pre></td></tr></table></figure>

<p>看下 ClickHouse 的同步位点：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">$ cat ckdatas/metadata/ckdb/.metadata</span><br><span class="line">Version:	1</span><br><span class="line">Binlog File:	mysql-bin.000001</span><br><span class="line">Binlog Position:	913</span><br><span class="line">Data Version:	0</span><br></pre></td></tr></table></figure>

<h3 id="Delete"><a href="#Delete" class="headerlink" title="Delete"></a>Delete</h3><p>首先在 MySQL Master 上执行一个删除操作：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">delete</span> <span class="keyword">from</span> t1 <span class="keyword">where</span> a<span class="operator">=</span><span class="number">1</span>;</span><br><span class="line">Query OK, <span class="number">1</span> <span class="type">row</span> affected (<span class="number">0.01</span> sec)</span><br></pre></td></tr></table></figure>

<p>然后在 ClickHouse Slave 侧查看记录：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">clickhouse :) <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t1;</span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span> t1</span><br><span class="line"></span><br><span class="line">┌─a─┬─b─┐</span><br><span class="line">│ <span class="number">2</span> │ <span class="number">2</span> │</span><br><span class="line">└───┴───┘</span><br><span class="line"></span><br><span class="line"><span class="number">1</span> <span class="keyword">rows</span> <span class="keyword">in</span> set. Elapsed: <span class="number">0.032</span> sec.</span><br></pre></td></tr></table></figure>

<p>此时的 metadata 里 Data Version 已经递增到 2:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">cat ckdatas<span class="operator">/</span>metadata<span class="operator">/</span>ckdb<span class="operator">/</span>.metadata</span><br><span class="line">Version:	<span class="number">1</span></span><br><span class="line">Binlog File:	mysql<span class="operator">-</span>bin<span class="number">.000001</span></span><br><span class="line">Binlog Position:	<span class="number">1171</span></span><br><span class="line">Data Version:	<span class="number">2</span></span><br></pre></td></tr></table></figure>

<h3 id="Update"><a href="#Update" class="headerlink" title="Update"></a>Update</h3><p>MySQL Master:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t1;</span><br><span class="line"><span class="operator">+</span><span class="comment">---+------+</span></span><br><span class="line"><span class="operator">|</span> a <span class="operator">|</span> b    <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">---+------+</span></span><br><span class="line"><span class="operator">|</span> <span class="number">2</span> <span class="operator">|</span>    <span class="number">2</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">---+------+</span></span><br><span class="line"><span class="number">1</span> <span class="type">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql<span class="operator">&gt;</span> update t1 <span class="keyword">set</span> b<span class="operator">=</span>b<span class="operator">+</span><span class="number">1</span>;</span><br><span class="line"></span><br><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t1;</span><br><span class="line"><span class="operator">+</span><span class="comment">---+------+</span></span><br><span class="line"><span class="operator">|</span> a <span class="operator">|</span> b    <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">---+------+</span></span><br><span class="line"><span class="operator">|</span> <span class="number">2</span> <span class="operator">|</span>    <span class="number">3</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">---+------+</span></span><br><span class="line"><span class="number">1</span> <span class="type">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br></pre></td></tr></table></figure>

<p>ClickHouse Slave:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">clickhouse :) <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t1;</span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span> t1</span><br><span class="line"></span><br><span class="line">┌─a─┬─b─┐</span><br><span class="line">│ <span class="number">2</span> │ <span class="number">3</span> │</span><br><span class="line">└───┴───┘</span><br><span class="line"></span><br><span class="line"><span class="number">1</span> <span class="keyword">rows</span> <span class="keyword">in</span> set. Elapsed: <span class="number">0.023</span> sec.</span><br></pre></td></tr></table></figure>

<h3 id="性能测试"><a href="#性能测试" class="headerlink" title="性能测试"></a>性能测试</h3><h4 id="测试环境"><a href="#测试环境" class="headerlink" title="测试环境"></a>测试环境</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">MySQL          8C16G 云主机, 192.168.0.3，基础数据 10188183 条记录</span><br><span class="line">ClickHouse     8C16G 云主机, 192.168.0.4</span><br><span class="line">benchyou       8C8G  云主机, 192.168.0.5, 256并发写, https://github.com/xelabs/benchyou</span><br></pre></td></tr></table></figure>

<p>性能测试跟硬件环境有较大关系，这里使用的是云主机模式，数据供参考。</p>
<h4 id="全量性能"><a href="#全量性能" class="headerlink" title="全量性能"></a>全量性能</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">8</span>c16G<span class="operator">-</span>vm :) <span class="keyword">create</span> database sbtest engine<span class="operator">=</span>MaterializeMySQL(<span class="string">&#x27;192.168.0.3:3306&#x27;</span>, <span class="string">&#x27;sbtest&#x27;</span>, <span class="string">&#x27;test&#x27;</span>, <span class="string">&#x27;123&#x27;</span>);</span><br><span class="line"></span><br><span class="line"><span class="number">8</span>c16G<span class="operator">-</span>vm :) watch lv1;</span><br><span class="line"></span><br><span class="line">WATCH lv1</span><br><span class="line"></span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│       <span class="number">0</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-29</span> <span class="number">06</span>:<span class="number">36</span>:<span class="number">04</span> │        <span class="number">1</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│ <span class="number">1113585</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-29</span> <span class="number">06</span>:<span class="number">36</span>:<span class="number">05</span> │        <span class="number">2</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│ <span class="number">2227170</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-29</span> <span class="number">06</span>:<span class="number">36</span>:<span class="number">07</span> │        <span class="number">3</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│ <span class="number">3340755</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-29</span> <span class="number">06</span>:<span class="number">36</span>:<span class="number">10</span> │        <span class="number">4</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│ <span class="number">4454340</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-29</span> <span class="number">06</span>:<span class="number">36</span>:<span class="number">13</span> │        <span class="number">5</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│ <span class="number">5567925</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-29</span> <span class="number">06</span>:<span class="number">36</span>:<span class="number">16</span> │        <span class="number">6</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│ <span class="number">6681510</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-29</span> <span class="number">06</span>:<span class="number">36</span>:<span class="number">18</span> │        <span class="number">7</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│ <span class="number">7795095</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-29</span> <span class="number">06</span>:<span class="number">36</span>:<span class="number">22</span> │        <span class="number">8</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│ <span class="number">8908680</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-29</span> <span class="number">06</span>:<span class="number">36</span>:<span class="number">25</span> │        <span class="number">9</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌──<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│ <span class="number">10022265</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-29</span> <span class="number">06</span>:<span class="number">36</span>:<span class="number">28</span> │       <span class="number">10</span> │</span><br><span class="line">└──────────┴─────────────────────┴──────────┘</span><br><span class="line">┌──<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│ <span class="number">10188183</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-29</span> <span class="number">06</span>:<span class="number">36</span>:<span class="number">28</span> │       <span class="number">11</span> │</span><br><span class="line">└──────────┴─────────────────────┴──────────┘</span><br><span class="line">← Progress: <span class="number">11.00</span> <span class="keyword">rows</span>, <span class="number">220.00</span> B (<span class="number">0.16</span> <span class="keyword">rows</span><span class="operator">/</span>s., <span class="number">3.17</span> B<span class="operator">/</span>s.)</span><br></pre></td></tr></table></figure>

<p>在这个硬件环境下，全量同步性能大概是 <strong>424507/s</strong>，<strong>42w</strong> 事务每秒。</p>
<p>因为全量的数据之间没有依赖关系，可以进一步优化成并行，加速同步。</p>
<p>全量的性能直接决定 ClickHouse slave 坏掉后重建的速度，如果你的 MySQL 有 <strong>10 亿</strong>条数据，大概 <strong>40 分钟</strong>就可以重建完成。</p>
<h4 id="增量性能-实时同步"><a href="#增量性能-实时同步" class="headerlink" title="增量性能(实时同步)"></a>增量性能(实时同步)</h4><p>在当前配置下，ClickHouse slave 单线程回放消费能力大于 MySQL master 256 并发下生产能力，通过测试可以看到它们保持<strong>实时同步</strong>。</p>
<p>benchyou 压测数据，<strong>2.1w</strong> 事务/秒(MySQL 在当前环境下TPS上不去):</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">./bin/benchyou --mysql-host=192.168.0.3 --mysql-user=test --mysql-password=123 --oltp-tables-count=1 --write-threads=256 --read-threads=0</span><br><span class="line"></span><br><span class="line">time            thds               tps     wtps    rtps</span><br><span class="line">[13s]        [r:0,w:256,u:0,d:0]  19962    19962   0</span><br><span class="line"></span><br><span class="line">time            thds               tps     wtps    rtps</span><br><span class="line">[14s]        [r:0,w:256,u:0,d:0]  20415    20415   0</span><br><span class="line"></span><br><span class="line">time            thds               tps     wtps    rtps</span><br><span class="line">[15s]        [r:0,w:256,u:0,d:0]  21131    21131   0</span><br><span class="line"></span><br><span class="line">time            thds               tps     wtps    rtps</span><br><span class="line">[16s]        [r:0,w:256,u:0,d:0]  21606    21606   0</span><br><span class="line"></span><br><span class="line">time            thds               tps     wtps    rtps</span><br><span class="line">[17s]        [r:0,w:256,u:0,d:0]  22505    22505   0</span><br></pre></td></tr></table></figure>

<p>ClickHouse 侧单线程回放能力，<strong>2.1w</strong> 事务/秒，实时同步：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br></pre></td><td class="code"><pre><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│  <span class="number">150732</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-30</span> <span class="number">05</span>:<span class="number">17</span>:<span class="number">15</span> │       <span class="number">17</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│  <span class="number">155477</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-30</span> <span class="number">05</span>:<span class="number">17</span>:<span class="number">16</span> │       <span class="number">18</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│  <span class="number">160222</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-30</span> <span class="number">05</span>:<span class="number">17</span>:<span class="number">16</span> │       <span class="number">19</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│  <span class="number">164967</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-30</span> <span class="number">05</span>:<span class="number">17</span>:<span class="number">16</span> │       <span class="number">20</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│  <span class="number">169712</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-30</span> <span class="number">05</span>:<span class="number">17</span>:<span class="number">16</span> │       <span class="number">21</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│  <span class="number">174457</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-30</span> <span class="number">05</span>:<span class="number">17</span>:<span class="number">16</span> │       <span class="number">22</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│  <span class="number">179202</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-30</span> <span class="number">05</span>:<span class="number">17</span>:<span class="number">17</span> │       <span class="number">23</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│  <span class="number">183947</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-30</span> <span class="number">05</span>:<span class="number">17</span>:<span class="number">17</span> │       <span class="number">24</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│  <span class="number">188692</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-30</span> <span class="number">05</span>:<span class="number">17</span>:<span class="number">17</span> │       <span class="number">25</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│  <span class="number">193437</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-30</span> <span class="number">05</span>:<span class="number">17</span>:<span class="number">17</span> │       <span class="number">26</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br><span class="line">┌─<span class="built_in">count</span>()─┬───────────────now()─┬─_version─┐</span><br><span class="line">│  <span class="number">198182</span> │ <span class="number">2020</span><span class="number">-07</span><span class="number">-30</span> <span class="number">05</span>:<span class="number">17</span>:<span class="number">17</span> │       <span class="number">27</span> │</span><br><span class="line">└─────────┴─────────────────────┴──────────┘</span><br></pre></td></tr></table></figure>

<h3 id="实现机制"><a href="#实现机制" class="headerlink" title="实现机制"></a>实现机制</h3><p>在探讨机制之前，首先需要了解下 MySQL 的 binlog event ，主要有以下几种类型：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">1. MYSQL_QUERY_EVENT　　　　-- DDL</span><br><span class="line">2. MYSQL_WRITE_ROWS_EVENT　-- insert数据</span><br><span class="line">3. MYSQL_UPDATE_ROWS_EVENT -- update数据</span><br><span class="line">4. MYSQL_DELETE_ROWS_EVENT -- delete数据</span><br></pre></td></tr></table></figure>

<p>当一个事务提交后，MySQL 会把执行的 SQL 处理成相应的 binlog event，并持久化到 binlog 文件。</p>
<p>binlog 是 MySQL 对外输出的重要途径，只要你实现 MySQL Replication Protocol，就可以流式的消费MySQL 生产的 binlog event，具体协议见 <a target="_blank" rel="noopener" href="https://dev.mysql.com/doc/internals/en/replication-protocol.html">Replication Protocol</a>。</p>
<p>由于历史原因，协议繁琐而诡异，这不是本文重点。</p>
<p>对于 ClickHouse 消费 MySQL binlog 来说，主要有以下３个难点：</p>
<ul>
<li>DDL 兼容</li>
<li>Delete/Update 支持</li>
<li>Query 过滤</li>
</ul>
<h4 id="DDL"><a href="#DDL" class="headerlink" title="DDL"></a>DDL</h4><p>DDL 兼容花费了大量的代码去实现。</p>
<p>首先，我们看看 MySQL 的表复制到 ClickHouse 后会变成什么样子。</p>
<p>MySQL master:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">show</span> <span class="keyword">create</span> <span class="keyword">table</span> t1\G;</span><br><span class="line"><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span> <span class="number">1.</span> <span class="type">row</span> <span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span><span class="operator">*</span></span><br><span class="line">       <span class="keyword">Table</span>: t1</span><br><span class="line"><span class="keyword">Create</span> <span class="keyword">Table</span>: <span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `t1` (</span><br><span class="line">  `a` <span class="type">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">  `b` <span class="type">int</span>(<span class="number">11</span>) <span class="keyword">DEFAULT</span> <span class="keyword">NULL</span>,</span><br><span class="line">  <span class="keyword">PRIMARY</span> KEY (`a`)</span><br><span class="line">) ENGINE<span class="operator">=</span>InnoDB <span class="keyword">DEFAULT</span> CHARSET<span class="operator">=</span>latin1</span><br></pre></td></tr></table></figure>

<p>ClickHouse slave:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">ATTACH <span class="keyword">TABLE</span> t1</span><br><span class="line">(</span><br><span class="line">    `a` Int32,</span><br><span class="line">    `b` Nullable(Int32),</span><br><span class="line">    `_sign` Int8,</span><br><span class="line">    `_version` UInt64</span><br><span class="line">)</span><br><span class="line">ENGINE <span class="operator">=</span> ReplacingMergeTree(_version)</span><br><span class="line"><span class="keyword">PARTITION</span> <span class="keyword">BY</span> intDiv(a, <span class="number">4294967</span>)</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> tuple(a)</span><br><span class="line">SETTINGS index_granularity <span class="operator">=</span> <span class="number">8192</span></span><br></pre></td></tr></table></figure>

<p>可以看到：</p>
<ul>
<li>默认增加了 2 个隐藏字段：<code>_sign</code>(-1删除, 1写入) 和 <code>_version</code>(数据版本)</li>
<li>引擎转换成了 ReplacingMergeTree，以 _version 作为 column version</li>
<li>原主键字段 a 作为排序和分区键</li>
</ul>
<p>这只是一个表的复制，其他还有非常多的DDL处理，比如增加列、索引等，感兴趣可以观摩 Parsers/MySQL 下代码。</p>
<h4 id="Update和Delete"><a href="#Update和Delete" class="headerlink" title="Update和Delete"></a>Update和Delete</h4><p>当我们在 MySQL master 执行：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">delete</span> <span class="keyword">from</span> t1 <span class="keyword">where</span> a<span class="operator">=</span><span class="number">1</span>;</span><br><span class="line">mysql<span class="operator">&gt;</span> update t1 <span class="keyword">set</span> b<span class="operator">=</span>b<span class="operator">+</span><span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<p>ClickHouse t1数据（把 <code>_sign</code> 和 <code>_version</code> 一并查询）：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">clickhouse :) <span class="keyword">select</span> a,b,_sign, _version <span class="keyword">from</span> t1;</span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    a,</span><br><span class="line">    b,</span><br><span class="line">    _sign,</span><br><span class="line">    _version</span><br><span class="line"><span class="keyword">FROM</span> t1</span><br><span class="line"></span><br><span class="line">┌─a─┬─b─┬─_sign─┬─_version─┐</span><br><span class="line">│ <span class="number">1</span> │ <span class="number">1</span> │     <span class="number">1</span> │        <span class="number">1</span> │</span><br><span class="line">│ <span class="number">2</span> │ <span class="number">2</span> │     <span class="number">1</span> │        <span class="number">1</span> │</span><br><span class="line">└───┴───┴───────┴──────────┘</span><br><span class="line">┌─a─┬─b─┬─_sign─┬─_version─┐</span><br><span class="line">│ <span class="number">1</span> │ <span class="number">1</span> │    <span class="number">-1</span> │        <span class="number">2</span> │</span><br><span class="line">└───┴───┴───────┴──────────┘</span><br><span class="line">┌─a─┬─b─┬─_sign─┬─_version─┐</span><br><span class="line">│ <span class="number">2</span> │ <span class="number">3</span> │     <span class="number">1</span> │        <span class="number">3</span> │</span><br><span class="line">└───┴───┴───────┴──────────┘</span><br></pre></td></tr></table></figure>

<p>根据返回结果，可以看到是由 3 个 part 组成。</p>
<p>part1 由 <code>mysql&gt; insert into t1 values(1,1),(2,2)</code> 生成：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">┌─a─┬─b─┬─_sign─┬─_version─┐</span><br><span class="line">│ 1 │ 1 │     1 │        1 │</span><br><span class="line">│ 2 │ 2 │     1 │        1 │</span><br><span class="line">└───┴───┴───────┴──────────┘</span><br></pre></td></tr></table></figure>

<p>part2 由 <code>mysql&gt; delete from t1 where a=1</code> 生成：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">┌─a─┬─b─┬─_sign─┬─_version─┐</span><br><span class="line">│ 1 │ 1 │    -1 │        2 │</span><br><span class="line">└───┴───┴───────┴──────────┘</span><br><span class="line">说明：</span><br><span class="line">_sign = -1表明处于删除状态</span><br></pre></td></tr></table></figure>

<p>part3 由 <code>update t1 set b=b+1</code> 生成：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">┌─a─┬─b─┬─_sign─┬─_version─┐</span><br><span class="line">│ 2 │ 3 │     1 │        3 │</span><br><span class="line">└───┴───┴───────┴──────────┘</span><br></pre></td></tr></table></figure>

<p>使用 final 查询：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line">clickhouse :) <span class="keyword">select</span> a,b,_sign,_version <span class="keyword">from</span> t1 <span class="keyword">final</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    a,</span><br><span class="line">    b,</span><br><span class="line">    _sign,</span><br><span class="line">    _version</span><br><span class="line"><span class="keyword">FROM</span> t1</span><br><span class="line"><span class="keyword">FINAL</span></span><br><span class="line"></span><br><span class="line">┌─a─┬─b─┬─_sign─┬─_version─┐</span><br><span class="line">│ <span class="number">1</span> │ <span class="number">1</span> │    <span class="number">-1</span> │        <span class="number">2</span> │</span><br><span class="line">└───┴───┴───────┴──────────┘</span><br><span class="line">┌─a─┬─b─┬─_sign─┬─_version─┐</span><br><span class="line">│ <span class="number">2</span> │ <span class="number">3</span> │     <span class="number">1</span> │        <span class="number">3</span> │</span><br><span class="line">└───┴───┴───────┴──────────┘</span><br><span class="line"></span><br><span class="line"><span class="number">2</span> <span class="keyword">rows</span> <span class="keyword">in</span> set. Elapsed: <span class="number">0.016</span> sec.</span><br></pre></td></tr></table></figure>

<p>可以看到 ReplacingMergeTree 已经根据 <code>_version</code> 和 OrderBy 对记录进行去重。</p>
<h4 id="Query"><a href="#Query" class="headerlink" title="Query"></a>Query</h4><p>MySQL master:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t1;</span><br><span class="line"><span class="operator">+</span><span class="comment">---+------+</span></span><br><span class="line"><span class="operator">|</span> a <span class="operator">|</span> b    <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">---+------+</span></span><br><span class="line"><span class="operator">|</span> <span class="number">2</span> <span class="operator">|</span>    <span class="number">3</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">---+------+</span></span><br><span class="line"><span class="number">1</span> <span class="type">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br></pre></td></tr></table></figure>

<p>ClickHouse slave:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">clickhouse :) <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t1;</span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span> t1</span><br><span class="line"></span><br><span class="line">┌─a─┬─b─┐</span><br><span class="line">│ <span class="number">2</span> │ <span class="number">3</span> │</span><br><span class="line">└───┴───┘</span><br><span class="line"></span><br><span class="line">clickhouse :) <span class="keyword">select</span> <span class="operator">*</span>,_sign,_version <span class="keyword">from</span> t1;</span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    <span class="operator">*</span>,</span><br><span class="line">    _sign,</span><br><span class="line">    _version</span><br><span class="line"><span class="keyword">FROM</span> t1</span><br><span class="line"></span><br><span class="line">┌─a─┬─b─┬─_sign─┬─_version─┐</span><br><span class="line">│ <span class="number">1</span> │ <span class="number">1</span> │    <span class="number">-1</span> │        <span class="number">2</span> │</span><br><span class="line">│ <span class="number">2</span> │ <span class="number">3</span> │     <span class="number">1</span> │        <span class="number">3</span> │</span><br><span class="line">└───┴───┴───────┴──────────┘</span><br><span class="line">说明：这里还有一条删除记录，_sign为<span class="number">-1</span></span><br></pre></td></tr></table></figure>

<p>MaterializeMySQL 被定义成一种存储引擎，所以在读取的时候，会根据 <code>_sign</code> 状态进行判断，如果是-1则是已经删除，进行过滤。</p>
<h3 id="并行回放"><a href="#并行回放" class="headerlink" title="并行回放"></a>并行回放</h3><p>为什么 MySQL 需要并行回放？</p>
<p>假设 MySQL master 有 1024 个并发同时写入、更新数据，瞬间产生大量的 binlog event ，MySQL slave 上只有一个线程一个 event 接着一个 event 式回放，于是 MySQL 实现了并行回放功能！</p>
<p>那么，MySQL slave 回放时能否完全(或接近)模拟出 master 当时的 1024 并发行为呢？</p>
<p>要想并行首先要解决的就是依赖问题：我们需要 master 标记出哪些 event 可以并行，哪些 event 有先后关系，因为它是第一现场。</p>
<p>MySQL 通过在 binlog 里增加:</p>
<ul>
<li>last_committed，相同则可以并行</li>
<li>sequece_number，较小先执行，描述先后依赖</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">last_committed=3   sequece_number=4   -- event1</span><br><span class="line">last_committed=4   sequece_number=5   -- event2</span><br><span class="line">last_committed=4   sequece_number=6   -- event3</span><br><span class="line">last_committed=5   sequece_number=7   -- event4</span><br></pre></td></tr></table></figure>

<p>event2 和 event3 则可以并行，event4 需要等待前面 event 完成才可以回放。</p>
<p>以上只是一个大体原理，目前 MySQL 有３种并行模式可以选择：</p>
<ol>
<li>基于 database 并行</li>
<li>基于 group commit 并行</li>
<li>基于主键不冲突的 write set 并行</li>
</ol>
<p>最大程度上让 MySQL slave加速回放，整套机制还是异常复杂的。</p>
<p>回到 ClickHouse slave 问题，我们采用的单线程回放，延迟已经不是主要问题，这是由它们的机制决定的：</p>
<ul>
<li>MySQL slave 回放时，需要把 binlog event 转换成 SQL，然后模拟 master 的写入，这种逻辑复制是导致性能低下的最重要原因。</li>
<li>而 ClickHouse 在回放上，直接把 binlog event 转换成 底层 block 结构，然后直接写入底层的存储引擎，接近于物理复制，可以理解为把 binlog event 直接回放到 InnoDB 的 page。</li>
</ul>
<h3 id="读取最新"><a href="#读取最新" class="headerlink" title="读取最新"></a>读取最新</h3><p>虽然 ClickHouse slave 回放非常快，接近于实时，如何在ClickHouse slave上总是读取到最新的数据呢？</p>
<p>其实非常简单，借助 MySQL binlog GTID 特性，每次读的时候，我们跟 ｍaster 做一次 executed_gtid 同步，然后等待这些 executed_gtid 回放完毕即可。</p>
<h3 id="数据一致性"><a href="#数据一致性" class="headerlink" title="数据一致性"></a>数据一致性</h3><p>对一致性要求较高的场景，我们怎么验证 MySQL master 的数据和 ClickHouse slave 的数据一致性呢？</p>
<p>这块初步想法是提供一个兼容 MySQL checksum 算法的函数，我们只需对比两边的 checksum 值即可。</p>
<h3 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h3><p>ClickHouse 实时复制同步 MySQL 数据是 upstream 2020 的一个 roadmap，在整体构架上比较有挑战一直无人接单，挑战主要来自两方面：</p>
<ul>
<li>对 MySQL 复制通道与协议非常熟悉</li>
<li>对 ClickHouse 整体机制非常熟悉</li>
</ul>
<p>这样，在两个本来有点遥远的山头中间架起了一座高速，这条 <a target="_blank" rel="noopener" href="https://github.com/ClickHouse/ClickHouse/pull/10851">10851号</a> 高速由 zhang1024(ClickHouse侧) 和 BohuTANG(MySQL复制) 两个修路工联合承建，目前已经合并到 upstream 分支。</p>
<p>关于同步 MySQL 的数据，目前大家的方案基本都是在中间安置一个 binlog 消费工具，这个工具对 event 进行解析，然后再转换成 ClickHouse 的 SQL 语句，写到 ClickHouse server，链路较长，性能损耗较大。</p>
<p><a target="_blank" rel="noopener" href="https://github.com/ClickHouse/ClickHouse/pull/10851">10851号</a> 高速是在 ClickHouse 内部实现一套 binlog 消费方案，然后根据 event 解析成 ClickHouse 内部的 block 结构，再直接回写到底层存储引擎，几乎是最高效的一种实现方式，实现与 MySQL 实时同步的能力，让分析更接近现实。</p>
<p>基于 database 级的复制，实现了多源复制的功能，如果复制通道坏掉，我们只需在 ClickHouse 侧删掉 database 再重建一次即可，非常快速、方便，OLTP+OLAP 就是这么简单！</p>
<p>要想富，先修路！</p>
<hr>
<p>欢迎关注我的微信公众号【数据库内核】：分享主流开源数据库和存储引擎相关技术。</p>
<img src="https://dbkernel-1306518848.cos.ap-beijing.myqcloud.com/wechat/my-wechat-official-account.png" width="400" height="400" alt="欢迎关注公众号数据库内核" align="center"/>


<table>
<thead>
<tr>
<th>标题</th>
<th>网址</th>
</tr>
</thead>
<tbody><tr>
<td>GitHub</td>
<td><a href="https://dbkernel.github.io/">https://dbkernel.github.io</a></td>
</tr>
<tr>
<td>知乎</td>
<td><a target="_blank" rel="noopener" href="https://www.zhihu.com/people/dbkernel/posts">https://www.zhihu.com/people/dbkernel/posts</a></td>
</tr>
<tr>
<td>思否（SegmentFault）</td>
<td><a target="_blank" rel="noopener" href="https://segmentfault.com/u/dbkernel">https://segmentfault.com/u/dbkernel</a></td>
</tr>
<tr>
<td>掘金</td>
<td><a target="_blank" rel="noopener" href="https://juejin.im/user/5e9d3ed251882538083fed1f/posts">https://juejin.im/user/5e9d3ed251882538083fed1f/posts</a></td>
</tr>
<tr>
<td>开源中国（oschina）</td>
<td><a target="_blank" rel="noopener" href="https://my.oschina.net/dbkernel">https://my.oschina.net/dbkernel</a></td>
</tr>
<tr>
<td>博客园（cnblogs）</td>
<td><a target="_blank" rel="noopener" href="https://www.cnblogs.com/dbkernel">https://www.cnblogs.com/dbkernel</a></td>
</tr>
</tbody></table>

      
    </div>
    <div class="article-footer">
      <blockquote class="mt-2x">
  <ul class="post-copyright list-unstyled">
    
    <li class="post-copyright-link hidden-xs">
      <strong>本文链接：</strong>
      <a href="http://dbkernel.github.io/2020/07/28/clickhouse-and-friends-09-mysql-replication/" title="源码分析 | ClickHouse和他的朋友们（9）MySQL实时复制与实现" target="_blank" rel="external">http://dbkernel.github.io/2020/07/28/clickhouse-and-friends-09-mysql-replication/</a>
    </li>
    
    <li class="post-copyright-license">
      <strong>版权声明： </strong> 本博客所有文章除特别声明外，均采用 <a href="http://creativecommons.org/licenses/by/4.0/deed.zh" target="_blank" rel="external">CC BY 4.0 CN 许可协议</a>，转载请注明出处！
    </li>
  </ul>
</blockquote>


<div class="panel panel-default panel-badger">
  <div class="panel-body">
    <figure class="media">
      <div class="media-left">
        <a href="https://github.com/dbkernel" target="_blank" class="img-burn thumb-sm visible-lg">
          <img src="../../../../images/avatar.png" class="img-rounded w-full" alt="">
        </a>
      </div>
      <div class="media-body">
        <h3 class="media-heading"><a href="https://github.com/dbkernel" target="_blank"><span class="text-dark">DBKernel</span><small class="ml-1x">资深数据库开发工程师</small></a></h3>
        <div>目前从事云数据库MySQL/PostgreSQL的研发工作，曾做过PGXC、Greenplum等分布式数据库的内核开发。热衷于研究主流数据库架构、源码，对关系型数据库 MySQL/PostgreSQL及分布式数据库有深入研究。</div>
      </div>
    </figure>
  </div>
</div>


    </div>
  </article>
  
    
  <section id="comments">
  	
           
    
  </section>


  
</div>

  <nav class="bar bar-footer clearfix" data-stick-bottom>
  <div class="bar-inner">
  
  <ul class="pager pull-left">
    
    <li class="prev">
      <a href="../../../08/20/clickhouse-and-friends-10-merge-tree-wal/" title="源码分析 | ClickHouse和他的朋友们（10）MergeTree Write-Ahead Log"><i class="icon icon-angle-left" aria-hidden="true"></i><span>&nbsp;&nbsp;上一篇</span></a>
    </li>
    
    
    <li class="next">
      <a href="../../26/clickhouse-and-friends-08-parser/" title="源码分析 | ClickHouse和他的朋友们 (８) 纯手工打造的SQL解析器"><span>下一篇&nbsp;&nbsp;</span><i class="icon icon-angle-right" aria-hidden="true"></i></a>
    </li>
    
    
    <li class="toggle-toc">
      <a class="toggle-btn collapsed" data-toggle="collapse" href="#collapseToc" aria-expanded="false" title="文章目录" role="button">
        <span>[&nbsp;</span><span>文章目录</span>
        <i class="text-collapsed icon icon-anchor"></i>
        <i class="text-in icon icon-close"></i>
        <span>]</span>
      </a>
    </li>
    
  </ul>
  
  
  <!-- Button trigger modal -->
  <button type="button" class="btn btn-fancy btn-donate pop-onhover bg-gradient-warning" data-toggle="modal" data-target="#donateModal"><span>赏</span></button>
  <!-- <div class="wave-icon wave-icon-danger btn-donate" data-toggle="modal" data-target="#donateModal">
    <div class="wave-circle"><span class="icon"><i class="icon icon-bill"></i></span></div>
  </div> -->
  
  
  <div class="bar-right">
    
    <div class="share-component" data-sites="weibo,qq,wechat,qzone,facebook,twitter,linkedin" data-mobile-sites="weibo,qq,wechat,qzone,linkedin"></div>
    
  </div>
  </div>
</nav>
  
<!-- Modal -->
<div class="modal modal-center modal-small modal-xs-full fade" id="donateModal" tabindex="-1" role="dialog">
  <div class="modal-dialog" role="document">
    <div class="modal-content donate">
      <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
      <div class="modal-body">
        <div class="donate-box">
          <div class="donate-head">
            <p>感谢您的支持，我会继续努力的!</p>
          </div>
          <div class="tab-content">
            <div role="tabpanel" class="tab-pane fade active in" id="alipay">
              <div class="donate-payimg">
                <img src="../../../../images/donate/alipayimg.png" alt="扫码支持" title="扫一扫" />
              </div>
              <p class="text-muted mv">扫码打赏，你说多少就多少</p>
              <p class="text-grey">打开支付宝扫一扫，即可进行扫码打赏哦</p>
            </div>
            <div role="tabpanel" class="tab-pane fade" id="wechatpay">
              <div class="donate-payimg">
                <img src="../../../../images/donate/wechatpayimg.png" alt="扫码支持" title="扫一扫" />
              </div>
              <p class="text-muted mv">扫码打赏，你说多少就多少</p>
              <p class="text-grey">打开微信扫一扫，即可进行扫码打赏哦</p>
            </div>
          </div>
          <div class="donate-footer">
            <ul class="nav nav-tabs nav-justified" role="tablist">
              <li role="presentation" class="active">
                <a href="#alipay" id="alipay-tab" role="tab" data-toggle="tab" aria-controls="alipay" aria-expanded="true"><i class="icon icon-alipay"></i> 支付宝</a>
              </li>
              <li role="presentation" class="">
                <a href="#wechatpay" role="tab" id="wechatpay-tab" data-toggle="tab" aria-controls="wechatpay" aria-expanded="false"><i class="icon icon-wepay"></i> 微信支付</a>
              </li>
            </ul>
          </div>
        </div>
      </div>
    </div>
  </div>
</div>



</main>

  <footer class="footer" itemscope itemtype="http://schema.org/WPFooter">
	
	
    <ul class="social-links">
    	
        <li><a href="../../../../https:/github.com/dbkernel" target="_blank" title="Github" data-toggle=tooltip data-placement=top><i class="icon icon-github"></i></a></li>
        
        <li><a href="../../../../atom.xml" target="_blank" title="Rss" data-toggle=tooltip data-placement=top><i class="icon icon-rss"></i></a></li>
        
    </ul>

    <div class="copyright">
    	
        &copy; 2022 DBKernel
        
        <div class="publishby">
        	Theme by <a href="https://github.com/cofess" target="_blank"> cofess </a>base on <a href="https://github.com/cofess/hexo-theme-pure" target="_blank">pure</a>.
        </div>
    </div>
</footer>
  <script src="//cdn.jsdelivr.net/npm/jquery@1.12.4/dist/jquery.min.js"></script>
<script>
window.jQuery || document.write('<script src="js/jquery.min.js"><\/script>')
</script>

<script src="../../../../js/plugin.min.js"></script>


<script src="../../../../js/application.js"></script>


    <script>
(function (window) {
    var INSIGHT_CONFIG = {
        TRANSLATION: {
            POSTS: '文章',
            PAGES: '页面',
            CATEGORIES: '分类',
            TAGS: '标签',
            UNTITLED: '(未命名)',
        },
        ROOT_URL: '/',
        CONTENT_URL: '../../../../content.json',
    };
    window.INSIGHT_CONFIG = INSIGHT_CONFIG;
})(window);
</script>

<script src="../../../../js/insight.js"></script>






   
<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>





   
    
  <!-- <link rel="stylesheet" href="https://unpkg.com/gitalk/dist/gitalk.css"> -->
  <script src="//cdn.jsdelivr.net/npm/gitalk@1.4.0/dist/gitalk.min.js"></script>
  <script src="//cdn.jsdelivr.net/npm/blueimp-md5@2.10.0/js/md5.min.js"></script>
  <script type="text/javascript">
  var gitalk = new Gitalk({
    clientID: '642675de9efee853bde8',
    clientSecret: 'e481d901d83ac31b4041437ec30a4fd00d112cfa',
    repo: 'dbkernel/gitalk-comment',
    owner: 'dbkernel',
    admin: ['dbkernel1989'],
    id: md5(location.pathname),
    distractionFreeMode: true
  })
  gitalk.render('comments')
  </script>

      






    <script defer>
var _hmt = _hmt || [];
(function() {
    var hm = document.createElement("script");
    hm.src = "//hm.baidu.com/hm.js?bf7f972e25b6a36ff4b828ba0214e515";
    var s = document.getElementsByTagName("script")[0];
    s.parentNode.insertBefore(hm, s);
})();
</script>



</body>
</html>